const mysql = require('mysql2/promise');

async function migrateGemBaseToNumeric() {
    const connection = await mysql.createConnection({
        host: '43.143.253.188',
        port: 3306,
        user: 'nextjs',
        password: 'yEAiRE2jcxTyRBy5',
        database: 'nextjs'
    });

    try {
        console.log('🚀 开始迁移gem_base表到数字类型装备槽位...');

        // 1. 删除外键约束
        console.log('🔗 删除外键约束...');
        try {
            await connection.execute('ALTER TABLE character_gem_inventory DROP FOREIGN KEY fk_character_gem_inventory_gem_base');
            console.log('✅ 删除character_gem_inventory外键约束成功');
        } catch (error) {
            console.log('⚠️ 外键约束可能不存在，继续执行...');
        }

        try {
            await connection.execute('ALTER TABLE equipment_gem_socket DROP FOREIGN KEY fk_equipment_gem_socket_gem_base');
            console.log('✅ 删除equipment_gem_socket外键约束成功');
        } catch (error) {
            console.log('⚠️ 外键约束可能不存在，继续执行...');
        }

        // 2. 删除现有表
        console.log('🗑️ 删除现有gem_base表...');
        await connection.execute('DROP TABLE IF EXISTS gem_base');

        // 3. 创建新表结构
        console.log('🏗️ 创建新的gem_base表结构...');
        const createTableSQL = `
    CREATE TABLE \`gem_base\` (
      \`id\` int NOT NULL AUTO_INCREMENT,
      \`name\` varchar(50) NOT NULL,
      \`image\` varchar(100) DEFAULT NULL,
      \`max_level\` int NOT NULL DEFAULT 10,
      \`equipment_slots\` json NOT NULL COMMENT '可镶嵌的装备槽位数组，1头盔 2武器 3铠甲 4项链 5护腕 6鞋子',
      \`possible_attributes\` json NOT NULL,
      \`created_at\` datetime DEFAULT CURRENT_TIMESTAMP,
      \`updated_at\` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (\`id\`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='宝石基础信息表';
    `;
        await connection.execute(createTableSQL);

        // 4. 插入新的宝石数据（使用数字数组）
        console.log('💎 插入新的宝石数据...');
        const insertSQL = `
    INSERT INTO \`gem_base\` (\`name\`, \`image\`, \`max_level\`, \`equipment_slots\`, \`possible_attributes\`) VALUES
    ('钻石', 'diamond.png', 10, '[1, 3, 4]', 
     '[{"type": 1, "name": "抗物理", "maxValue": 15}, {"type": 7, "name": "暴击率", "maxValue": 15}, {"type": 15, "name": "速度", "maxValue": 15}]'),
    ('水晶', 'crystal.png', 10, '[2, 5]', 
     '[{"type": 11, "name": "抗妖火", "maxValue": 15}, {"type": 18, "name": "力量", "maxValue": 15}, {"type": 1, "name": "抗物理", "maxValue": 15}]'),
    ('玛瑙', 'agate.png', 10, '[4, 6]', 
     '[{"type": 3, "name": "抗落雷", "maxValue": 15}, {"type": 15, "name": "敏捷", "maxValue": 15}, {"type": 13, "name": "攻击", "maxValue": 15}]'),
    ('白玉', 'white_jade.png', 10, '[1, 5]', 
     '[{"type": 4, "name": "抗毒术", "maxValue": 15}, {"type": 17, "name": "气血", "maxValue": 15}, {"type": 14, "name": "防御", "maxValue": 15}]'),
    ('琥珀', 'amber.png', 10, '[4, 3]', 
     '[{"type": 5, "name": "反震率", "maxValue": 15}, {"type": 18, "name": "精力", "maxValue": 15}, {"type": 8, "name": "抗围困", "maxValue": 15}]'),
    ('翡翠', 'emerald.png', 10, '[3, 6]', 
     '[{"type": 6, "name": "躲避率", "maxValue": 15}, {"type": 13, "name": "攻击", "maxValue": 15}, {"type": 9, "name": "抗扰乱", "maxValue": 15}]'),
    ('珍珠', 'pearl.png', 10, '[1, 4, 2, 5]', 
     '[{"type": 19, "name": "命中率", "maxValue": 15}, {"type": 14, "name": "防御", "maxValue": 15}, {"type": 20, "name": "抗封锁", "maxValue": 15}]'),
    ('绿宝石', 'green_gem.png', 10, '[3, 6]', 
     '[{"type": 8, "name": "抗围困", "maxValue": 15}, {"type": 21, "name": "抗风沙", "maxValue": 15}, {"type": 22, "name": "反击率", "maxValue": 15}]'),
    ('红宝石', 'ruby.png', 10, '[1, 2, 5]', 
     '[{"type": 9, "name": "抗扰乱", "maxValue": 15}, {"type": 11, "name": "抗妖火", "maxValue": 15}, {"type": 23, "name": "致命率", "maxValue": 15}]'),
    ('蓝宝石', 'sapphire.png', 10, '[1, 3]', 
     '[{"type": 20, "name": "抗封锁", "maxValue": 15}, {"type": 3, "name": "抗落雷", "maxValue": 15}, {"type": 17, "name": "体质", "maxValue": 15}]'),
    ('猫眼石', 'cat_eye.png', 10, '[4, 3]', 
     '[{"type": 21, "name": "抗风沙", "maxValue": 15}, {"type": 4, "name": "抗毒术", "maxValue": 15}, {"type": 16, "name": "智力", "maxValue": 15}]'),
    ('金刚玉', 'vajra_jade.png', 10, '[2, 5, 6]', 
     '[{"type": 7, "name": "暴击率", "maxValue": 15}, {"type": 24, "name": "暴击力", "maxValue": 15}, {"type": 25, "name": "穿透率", "maxValue": 15}]'),
    ('龙血石', 'dragon_blood_stone.png', 10, '[2, 5]', 
     '[{"type": 26, "name": "法爆率", "maxValue": 15}, {"type": 27, "name": "爆伤力", "maxValue": 15}, {"type": 24, "name": "暴击力", "maxValue": 15}]'),
    ('黑曜石', 'obsidian.png', 10, '[2, 5, 6]', 
     '[{"type": 26, "name": "法爆率", "maxValue": 15}, {"type": 28, "name": "法伤力", "maxValue": 15}, {"type": 29, "name": "法穿率", "maxValue": 15}]'),
    ('乾坤珠', 'qiankun_pearl.png', 10, '[2, 5]', 
     '[{"type": 30, "name": "连击率", "maxValue": 15}, {"type": 31, "name": "连击数", "maxValue": 15}, {"type": 7, "name": "暴击率", "maxValue": 15}]');
    `;

        await connection.execute(insertSQL);

        // 5. 重新添加外键约束
        console.log('🔗 重新添加外键约束...');
        try {
            await connection.execute('ALTER TABLE character_gem_inventory ADD CONSTRAINT fk_character_gem_inventory_gem_base FOREIGN KEY (gemBaseId) REFERENCES gem_base(id) ON DELETE CASCADE');
            console.log('✅ 添加character_gem_inventory外键约束成功');
        } catch (error) {
            console.log('⚠️ 添加外键约束失败:', error.message);
        }

        try {
            await connection.execute('ALTER TABLE equipment_gem_socket ADD CONSTRAINT fk_equipment_gem_socket_gem_base FOREIGN KEY (gemBaseId) REFERENCES gem_base(id) ON DELETE CASCADE');
            console.log('✅ 添加equipment_gem_socket外键约束成功');
        } catch (error) {
            console.log('⚠️ 添加外键约束失败:', error.message);
        }

        console.log('✅ gem_base表迁移完成！');
        console.log('📋 装备槽位映射: 1头盔 2武器 3铠甲 4项链 5护腕 6鞋子');

    } catch (error) {
        console.error('❌ 迁移失败:', error);
        throw error;
    } finally {
        await connection.end();
    }
}

// 运行迁移
migrateGemBaseToNumeric().catch(console.error);
